--    Program: 		CAPPOIMP_APPROVAL_GROUP_RULE.sql
--    Description:  Import PO APPROVAL GROUP RULE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_control_group_id		number;
   l_control_rule_id		number;
                    		                 		
   cursor cur_po_control_rules is
      select * from CRST_PO_CONTROL_RULES
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_control_rules loop   	
     l_err_msg_tmp := '';
     
     l_control_group_id := null; 	
     begin
       SELECT control_group_id_n
       INTO   l_control_group_id
       FROM   CRST_PO_CONTROL_GROUPS_ALL
       WHERE  control_group_id = cur.CONTROL_GROUP_ID;
     exception
       when others then
         l_err_msg_tmp := 'New Control Group ID '||cur.CONTROL_GROUP_ID||' not found';
     end; 
     	
     select PO_CONTROL_RULES_S.NEXTVAL 
     into l_control_rule_id
     FROM DUAL;
     
     update crst_po_control_rules
     set    control_rule_id_n = l_control_rule_id
     where  control_rule_id = cur.CONTROL_RULE_ID;
     
     begin 
       -- import data
       insert into PO_CONTROL_RULES
       (
          CONTROL_RULE_ID         ,
          CONTROL_GROUP_ID        ,
          LAST_UPDATE_DATE        ,
          LAST_UPDATED_BY         ,
          LAST_UPDATE_LOGIN       ,
          CREATION_DATE           ,
          CREATED_BY              ,
          PROGRAM_UPDATE_DATE     ,
          REQUEST_ID              ,
          PROGRAM_APPLICATION_ID  ,
          PROGRAM_ID              ,
          RULE_TYPE_CODE          ,
          OBJECT_CODE             ,
          AMOUNT_LIMIT            ,
          LOCATION_ID             ,
          SEGMENT14_HIGH          ,
          INACTIVE_DATE           ,
          SEGMENT12_LOW           ,
          SEGMENT12_HIGH          ,
          SEGMENT1_LOW            ,
          SEGMENT13_LOW           ,
          SEGMENT1_HIGH           ,
          SEGMENT13_HIGH          ,
          SEGMENT2_LOW            ,
          SEGMENT14_LOW           ,
          SEGMENT2_HIGH           ,
          SEGMENT3_LOW            ,
          SEGMENT15_LOW           ,
          SEGMENT3_HIGH           ,
          SEGMENT15_HIGH          ,
          SEGMENT4_LOW            ,
          SEGMENT16_LOW           ,
          SEGMENT4_HIGH           ,
          SEGMENT16_HIGH          ,
          SEGMENT5_LOW            ,
          SEGMENT17_LOW           ,
          SEGMENT5_HIGH           ,
          SEGMENT17_HIGH          ,
          SEGMENT6_LOW            ,
          SEGMENT18_LOW           ,
          SEGMENT6_HIGH           ,
          SEGMENT18_HIGH          ,
          SEGMENT7_LOW            ,
          SEGMENT19_LOW           ,
          SEGMENT7_HIGH           ,
          SEGMENT19_HIGH          ,
          SEGMENT8_LOW            ,
          SEGMENT20_LOW           ,
          SEGMENT8_HIGH           ,
          SEGMENT20_HIGH          ,
          SEGMENT9_LOW            ,
          SEGMENT21_LOW           ,
          SEGMENT9_HIGH           ,
          SEGMENT10_LOW           ,
          SEGMENT21_HIGH          ,
          SEGMENT10_HIGH          ,
          SEGMENT22_LOW           ,
          SEGMENT11_LOW           ,
          SEGMENT22_HIGH          ,
          SEGMENT11_HIGH          ,
          SEGMENT23_LOW           ,
          SEGMENT23_HIGH          ,
          SEGMENT24_LOW           ,
          SEGMENT24_HIGH          ,
          SEGMENT25_LOW           ,
          SEGMENT25_HIGH          ,
          SEGMENT26_LOW           ,
          SEGMENT26_HIGH          ,
          SEGMENT27_LOW           ,
          SEGMENT27_HIGH          ,
          SEGMENT28_LOW           ,
          SEGMENT28_HIGH          ,
          SEGMENT29_LOW           ,
          SEGMENT29_HIGH          ,
          SEGMENT30_LOW           ,
          SEGMENT30_HIGH          ,
          ATTRIBUTE1              ,
          ATTRIBUTE2              ,
          ATTRIBUTE3              ,
          ATTRIBUTE4              ,
          ATTRIBUTE5              ,
          ATTRIBUTE6              ,
          ATTRIBUTE7              ,
          ATTRIBUTE8              ,
          ATTRIBUTE9              ,
          ATTRIBUTE10             ,
          ATTRIBUTE11             ,
          ATTRIBUTE12             ,
          ATTRIBUTE13             ,
          ATTRIBUTE14             ,
          ATTRIBUTE15             ,
          ATTRIBUTE_CATEGORY       
       )
       values
       (
            l_control_rule_id           ,
            l_control_group_id          ,
            cur.LAST_UPDATE_DATE        ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_LOGIN       ,
            cur.CREATION_DATE           ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.PROGRAM_UPDATE_DATE     ,
            cur.REQUEST_ID              ,
            cur.PROGRAM_APPLICATION_ID  ,
            cur.PROGRAM_ID              ,
            cur.RULE_TYPE_CODE          ,
            cur.OBJECT_CODE             ,
            cur.AMOUNT_LIMIT            ,
            cur.LOCATION_ID,
            cur.SEGMENT14_HIGH          ,
            cur.INACTIVE_DATE           ,
            cur.SEGMENT12_LOW           ,
            cur.SEGMENT12_HIGH          ,
            cur.SEGMENT1_LOW            ,
            cur.SEGMENT13_LOW           ,
            cur.SEGMENT1_HIGH           ,
            cur.SEGMENT13_HIGH          ,
            cur.SEGMENT2_LOW            ,
            cur.SEGMENT14_LOW           ,
            cur.SEGMENT2_HIGH           ,
            cur.SEGMENT3_LOW            ,
            cur.SEGMENT15_LOW           ,
            cur.SEGMENT3_HIGH           ,
            cur.SEGMENT15_HIGH          ,
            cur.SEGMENT4_LOW            ,
            cur.SEGMENT16_LOW           ,
            cur.SEGMENT4_HIGH           ,
            cur.SEGMENT16_HIGH          ,
            cur.SEGMENT5_LOW            ,
            cur.SEGMENT17_LOW           ,
            cur.SEGMENT5_HIGH           ,
            cur.SEGMENT17_HIGH          ,
            cur.SEGMENT6_LOW            ,
            cur.SEGMENT18_LOW           ,
            cur.SEGMENT6_HIGH           ,
            cur.SEGMENT18_HIGH          ,
            cur.SEGMENT7_LOW            ,
            cur.SEGMENT19_LOW           ,
            cur.SEGMENT7_HIGH           ,
            cur.SEGMENT19_HIGH          ,
            cur.SEGMENT8_LOW            ,
            cur.SEGMENT20_LOW           ,
            cur.SEGMENT8_HIGH           ,
            cur.SEGMENT20_HIGH          ,
            cur.SEGMENT9_LOW            ,
            cur.SEGMENT21_LOW           ,
            cur.SEGMENT9_HIGH           ,
            cur.SEGMENT10_LOW           ,
            cur.SEGMENT21_HIGH          ,
            cur.SEGMENT10_HIGH          ,
            cur.SEGMENT22_LOW           ,
            cur.SEGMENT11_LOW           ,
            cur.SEGMENT22_HIGH          ,
            cur.SEGMENT11_HIGH          ,
            cur.SEGMENT23_LOW           ,
            cur.SEGMENT23_HIGH          ,
            cur.SEGMENT24_LOW           ,
            cur.SEGMENT24_HIGH          ,
            cur.SEGMENT25_LOW           ,
            cur.SEGMENT25_HIGH          ,
            cur.SEGMENT26_LOW           ,
            cur.SEGMENT26_HIGH          ,
            cur.SEGMENT27_LOW           ,
            cur.SEGMENT27_HIGH          ,
            cur.SEGMENT28_LOW           ,
            cur.SEGMENT28_HIGH          ,
            cur.SEGMENT29_LOW           ,
            cur.SEGMENT29_HIGH          ,
            cur.SEGMENT30_LOW           ,
            cur.SEGMENT30_HIGH          ,
            cur.ATTRIBUTE1              ,
            cur.ATTRIBUTE2              ,
            cur.ATTRIBUTE3              ,
            cur.ATTRIBUTE4              ,
            cur.ATTRIBUTE5              ,
            cur.ATTRIBUTE6              ,
            cur.ATTRIBUTE7              ,
            cur.ATTRIBUTE8              ,
            cur.ATTRIBUTE9              ,
            cur.ATTRIBUTE10             ,
            cur.ATTRIBUTE11             ,
            cur.ATTRIBUTE12             ,
            cur.ATTRIBUTE13             ,
            cur.ATTRIBUTE14             ,
            cur.ATTRIBUTE15             ,
            cur.ATTRIBUTE_CATEGORY      
       ); 
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_APPROVAL_GROUP_RULE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
